package cn.big.bigd.ybp.dao;

import java.util.List;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.big.bigd.ybp.model.YbpUser;

/**
 * 
 * @author wangfan
 *
 */
@Repository
public interface YbpUserDAO extends JpaRepository<YbpUser, Long> {
	YbpUser findByUsername(String username);

    @Query("SELECT CASE WHEN COUNT(u) > 0 THEN true ELSE false END FROM YbpUser u WHERE u.username = :username")
    boolean existsByUsername(@Param("username") String username);
    
    Long countByStatus(Integer status);
    Long countByStatusAndThirteenLunch(Integer status,Integer thirteenLunch);
    Long countByStatusAndTwelveDinner(Integer status,Integer twelveDinner);
    Long countByStatusAndThirteenDinner(Integer status,Integer thirteenDinner);

    YbpUser findByFirstNameAndLastName(String firstName, String lastName);
    
    YbpUser findByEmail(String email);
    YbpUser findByEmailAndToken(String email,String token);
    YbpUser findByEmailAndPassword(String email,String password);
    YbpUser findByStatusAndToken(Integer status,String token);
    YbpUser findByEmailAndStatus(String email,Integer status);

    List<YbpUser> findByUserTypeAndDepartment(String userType, String department);

    List<YbpUser> findAll(Specification specification);
    
    List<YbpUser> findByStatus(Integer status);
   // convert(u.username using gbk)
    @Query(value ="select  *  from ybp_user    where status = ?1  order by convert(username using gbk) asc", nativeQuery = true)
    List<YbpUser> findByStatusOrderByUsernameAsc(Integer status);


    @Query(value ="select  *  from ybp_user    where status = ?1 and user_type =?2 order by convert(username using gbk) asc", nativeQuery = true)
    List<YbpUser> findByStatusAndUserTypeOrderByUsernameAsc(Integer status,String userType);

    @Query(value ="select  *  from ybp_user    where status = ?1 and user_type <>?2 order by convert(username using gbk) asc", nativeQuery = true)
    List<YbpUser> findByStatusAndUserTypeNotOrderByUsernameAsc(Integer status,String userType);

    @Query(value ="select  *  from ybp_user    where status = ?1 and user_type =?2 and issue is not null and issue <> \"\" order by convert(username using gbk) asc", nativeQuery = true)
    List<YbpUser> findByStatusAndUserTypeAndIssueNotNullOrderByUsernameAsc(Integer status,String userType);


    List<YbpUser> findByStatusAndThirteenLunch(Integer status,Integer thirteenLunch);
    List<YbpUser> findByStatusAndThirteenDinner(Integer status,Integer thirteenDinner);
    List<YbpUser> findByStatusAndTwelveDinner(Integer status,Integer twelveDinner);
//
//    @Modifying(clearAutomatically = true)
//    @Transactional
//    @Query("update  YbpUser set ybpuser.picPath = :picPath where ybpuser.id = :id")
//    int updatePicPath(@Param("picPath")String picPath, @Param("id") Long id);
//    
    @Modifying(clearAutomatically = true)
    @Transactional
    @Query("update YbpUser u set u.picPath = ?1 where u.id = ?2")
    int updatePicPath(String picPath, Long id);
    @Modifying
    @Transactional
    @Query("update YbpUser u set u.status = ?1 where u.id = ?2")
    int updateStatus(Integer status, Long id);
}
